﻿-- =============================================
-- Author:		<于大明>
-- Create date: <2018-09-03>
-- Description:	<同步LayIM表>
-- Example:  exec usp_FW_SyncLayIM 1000
-- =============================================
ALTER PROCEDURE [dbo].[usp_FW_SyncLayIM]
(
	@UserIntID BIGINT=0
)
AS
BEGIN
    IF OBJECT_ID('tempdb..#SyncMyUser') IS NOT NULL
    BEGIN
        DROP TABLE #SyncMyUser;
    END;

    -- 这次要同步的用户 
    SELECT mu.*
    INTO #SyncMyUser
    FROM dbo.FW_User mu
        LEFT JOIN dbo.layim_user lu
            ON mu.UserIntID = lu.id
    WHERE UserIntID=(CASE WHEN @UserIntID<>0 THEN @UserIntID ELSE UserIntID END )

    --1 同步layim用户表
    INSERT INTO dbo.layim_user
    (
        id,
        name,
        avatar,
        sign,
        status
    )
    SELECT a.UserIntID,
           a.UserName,
           ISNULL(a.UserImageUrl, '/images/photo/xiaopanzi.jpg'),
           '',
           'offline'
    FROM #SyncMyUser a
	LEFT JOIN dbo.layim_user b ON a.UserIntID=b.id
	WHERE b.id IS NULL;

    --2 同步好友分组表
    --2.1 如果是管理员的话那么就创建网站用户组
    INSERT INTO dbo.layim_friend_group
    (
        name,
        create_by
    )
    SELECT '网站用户',
           sy.UserIntID
    FROM #SyncMyUser sy
        LEFT JOIN layim_friend_group lf
            ON sy.UserIntID = lf.create_by
               AND lf.name = '网站用户'
    WHERE sy.UserIsAdmin = 1
          AND lf.id IS NULL;


 -- 创建公司的组
    INSERT INTO dbo.layim_friend_group
    (
        name,
        create_by
    )
    SELECT bu.BuUnitName,
           sy.UserIntID
    FROM #SyncMyUser sy
        LEFT JOIN dbo.FW_BuUnit bu
            ON bu.BuUnitNewGUID = sy.BuUnitNewGUID
        LEFT JOIN layim_friend_group lf
            ON sy.UserIntID = lf.create_by
               AND lf.name = bu.BuUnitName
    WHERE 
          lf.id IS NULL
          AND bu.BuUnitName IS NOT NULL;

 --   -------------------------------好友关系插入----------------------------

    -- 把用户加入到管理员的组中
    INSERT INTO dbo.layim_friend_relation
    (
        uid1,
        uid2,
        friend_group_1,
        friend_group_2
    )
    SELECT sy.UserIntID,
           mu.UserIntID,
           lfg.id,
           0
    FROM #SyncMyUser sy
        CROSS JOIN dbo.FW_User mu
        INNER JOIN dbo.layim_friend_group lfg
            ON lfg.name = '网站用户'
               AND lfg.create_by = sy.UserIntID
        LEFT JOIN layim_friend_relation lfr
            ON lfr.uid1 = sy.UserIntID
               AND lfr.uid2 = mu.UserIntID
               AND lfr.friend_group_1 = lfg.id
    WHERE sy.UserIsAdmin = 1
          AND lfr.id IS NULL
          AND mu.UserIntID <> sy.UserIntID;


    ---- 把该公司的下用户插入
    INSERT INTO dbo.layim_friend_relation
    (
        uid1,
        uid2,
        friend_group_1,
        friend_group_2
    )
    SELECT sy.UserIntID,
           mu.UserIntID,
           lfg.id,
           0
    FROM #SyncMyUser sy
        CROSS JOIN dbo.FW_User mu
        INNER JOIN dbo.FW_BuUnit  rb
            ON rb.BuUnitNewGUID = sy.BuUnitNewGUID
               AND rb.BuUnitName IS NOT NULL
        INNER JOIN dbo.layim_friend_group lfg
            ON lfg.name = rb.BuUnitName
               AND lfg.create_by = sy.UserIntID
        LEFT JOIN layim_friend_relation lfr
            ON lfr.uid1 = sy.UserIntID
               AND lfr.uid2 = mu.UserIntID
               AND lfr.friend_group_1 = lfg.id
    WHERE lfr.id IS NULL
          AND mu.BuUnitNewGUID = sy.BuUnitNewGUID
          AND mu.UserIntID <> sy.UserIntID;
 --   ---------------------------群数据维护--------------------------
	
	-- 创建网站的群
	INSERT INTO dbo.layim_big_group
    (
        name,
        avatar,
        create_by
    )
    SELECT TOP 1  '网站用户群',
			'/images/photo/huangzu.jpg',
			sm.UserIntID
	FROM #SyncMyUser sm
	WHERE sm.UserIsAdmin=1
	AND NOT EXISTS (SELECT TOP 1 1 FROM layim_big_group lb WHERE lb.name='网站用户群' )

	DECLARE @UserGroupId AS BIGINT
	SELECT TOP 1 @UserGroupId=id FROM layim_big_group lb WHERE lb.name='网站用户群' 
	-- 把网站的所有用户都插入进来
	 INSERT INTO dbo.layim_group_member
	 (
	     group_id,
	     member_id,
	     addtime
	 )		
	SELECT  @UserGroupId,
			sm.UserIntID,
			GETDATE()
	FROM #SyncMyUser sm
  WHERE NOT EXISTS (SELECT TOP 1 1 FROM layim_group_member lb WHERE group_id=@UserGroupId AND member_id=sm.UserIntID )
		
	-- 创建公司自己的群
	INSERT INTO dbo.layim_big_group
    (
        name,
        avatar,
        create_by
    )
    SELECT  BuUnitName,
			'/images/photo/huangzu.jpg',
			sm.UserIntID
	FROM #SyncMyUser sm
	INNER JOIN dbo.FW_BuUnit  rb ON sm.BuUnitNewGUID=rb.BuUnitNewGUID 
	WHERE BuUnitName IS NOT NULL  AND sm.IsMainAccountUser=1
	AND NOT EXISTS (SELECT TOP 1 1 FROM layim_big_group lb WHERE rb.BuUnitName=lb.name )
	
	-- 群里面加入公司的用户
	 INSERT INTO dbo.layim_group_member
	 (
	     group_id,
	     member_id,
	     addtime
	 )		
	SELECT  lbg.id,
			sm.UserIntID,
			GETDATE()
	FROM #SyncMyUser sm
	INNER JOIN dbo.FW_BuUnit  rb ON sm.BuUnitNewGUID=rb.BuUnitNewGUID
	INNER JOIN layim_big_group lbg ON lbg.name=rb.BuUnitName 
	WHERE BuUnitName IS NOT NULL
	AND NOT EXISTS (SELECT TOP 1 1 FROM layim_group_member lb WHERE group_id=lbg.id AND member_id=sm.UserIntID )

END;